#!/usr/bin/python
# -*- coding:utf-8 -*-
import re

# 添加数据表功能文件
from PySide2 import QtWidgets, QtGui, QtCore
from PySide2.QtWidgets import QFileDialog, QMessageBox, QComboBox, QCheckBox
from addDatabaseTable_ui import Ui_MainWindow
import sqlite3

class addDatabaseTableWindow(QtWidgets.QMainWindow, Ui_MainWindow):
    def __init__(self, conn):
        super().__init__()
        self.setupUi(self)
        self.conn = conn
        self.db_file_path = ""
        # 将按钮的点击事件连接到自定义函数
        self.addFiledPushButton.clicked.connect(self.add_field)
        self.delFiledPushButton.clicked.connect(self.delete_field)
        self.closePushButton.clicked.connect(self.cancelWindow)

        # 初始化模型
        self.model_addDB = QtGui.QStandardItemModel()
        # 预加载函数
        self.set_model_to_addDB()





    # def create_connection(db_file):
    #     """创建一个数据库连接到 SQLite 数据库"""
    #     conn = None
    #     try:
    #         conn = sqlite3.connect(db_file)
    #         print(f"成功连接到 SQLite 数据库版本: {sqlite3.version}")
    #     except sqlite3.Error as e:
    #         print(f"连接到 SQLite 数据库失败: {e}")
    #     return conn
    #


# 创建数据库连接并获取表信息
    def create_connection(self):
        try:
            conn = sqlite3.connect(self.db_file_path)
            print("成功连接到数据库")
            self.get_table_info(conn)
        except sqlite3.Error as e:
            print(f"连接到数据库失败: {e}")

    # 获取数据库中的表信息
    def get_table_info(self, conn):
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            tables = cursor.fetchall()
            print(tables)
        except sqlite3.Error as e:
            print(f"获取表信息失败: {e}")
        finally:
            if conn:
                conn.close()

    '''
    设置模型
    '''
    def set_model_to_addDB(self):

        # 设置model
        self.model_addDB.setHorizontalHeaderItem(0, QtGui.QStandardItem("字段名"))
        self.model_addDB.setHorizontalHeaderItem(1, QtGui.QStandardItem("数据类型"))
        self.model_addDB.setHorizontalHeaderItem(2, QtGui.QStandardItem("长度"))
        self.model_addDB.setHorizontalHeaderItem(3, QtGui.QStandardItem("小数点"))
        self.model_addDB.setHorizontalHeaderItem(4, QtGui.QStandardItem("是否为null"))
        self.model_addDB.setHorizontalHeaderItem(5, QtGui.QStandardItem("是否为主键"))
        self.model_addDB.setHorizontalHeaderItem(6, QtGui.QStandardItem("注释"))

        # 建立 view 和 model 的联系,model 和
        self.tableAdd.setModel(self.model_addDB)

    # 添加字段
    def add_field(self):
        # 添加一行空行
        row = self.model_addDB.rowCount()
        self.model_addDB.setItem(row, 0, QtGui.QStandardItem(""))

        # 创建下拉选择框，并添加选项
        combo_box = QComboBox()
        combo_box.addItems(["INTEGER", "TEXT", "REAL", "BLOB"])

        # 添加数据类型选择框到表格中
        self.tableAdd.setIndexWidget(self.model_addDB.index(row, 1), combo_box)

        # 添加其他字段项
        length_item = QtGui.QStandardItem("不需要填写")
        decimal_item = QtGui.QStandardItem("不需要填写")

        # 是否为null 和 是否为主键 需要复选框
        from PySide2.QtWidgets import QCheckBox

        # 是否为null 和 是否为主键 需要复选框
        null_checkbox = QCheckBox()
        null_checkbox.setTristate(False)  # 确保复选框只有两种状态
        null_checkbox.setText("")  # 确保没有文本
        null_item = QtGui.QStandardItem()
        null_item.setCheckable(True)
        null_item.setEditable(False)  # 禁止直接编辑
        self.tableAdd.setIndexWidget(null_item.index(), null_checkbox)

        primary_key_checkbox = QCheckBox()
        primary_key_checkbox.setTristate(False)  # 确保复选框只有两种状态
        primary_key_item = QtGui.QStandardItem()
        primary_key_item.setCheckable(True)
        self.tableAdd.setIndexWidget(primary_key_item.index(), primary_key_checkbox)


        for item in [length_item, decimal_item]:
            item.setEditable(False)  # 设置不可编辑

        self.model_addDB.setItem(row, 2, length_item)
        self.model_addDB.setItem(row, 3, decimal_item)
        self.model_addDB.setItem(row, 4, null_item)
        self.model_addDB.setItem(row, 5, primary_key_item)
        self.model_addDB.setItem(row, 6, QtGui.QStandardItem(""))

        # 当数据类型选择变化时，更新长度和小数点填写框的提示文本
        combo_box.currentIndexChanged.connect(lambda index, r=row: self.data_type_changed(combo_box, r))

    def data_type_changed(self, combo_box, row):
        data_type = combo_box.currentText()
        length_item = self.model_addDB.item(row, 2)
        decimal_item = self.model_addDB.item(row, 3)
        if data_type in ["INTEGER", "TEXT", "BLOB"]:
            length_item.setText("不需要填写")
            decimal_item.setText("不需要填写")
            length_item.setEditable(False)
            decimal_item.setEditable(False)
        elif data_type == "REAL":
            length_item.setText("")
            decimal_item.setText("")
            length_item.setEditable(True)
            decimal_item.setEditable(True)

    # 删除字段
    def delete_field(self):
        selection_model = self.tableAdd.selectionModel()
        indexes = selection_model.selectedIndexes()
        if indexes:
            for index in indexes:
                self.model_addDB.removeRow(index.row())

    # 创建表
    def create_table(self):
        # 确保已连接到数据库
        self.create_connection()

        table_name = self.tableNameLineEdit.text().strip()
        if not table_name:
            QMessageBox.warning(self, "警告", "请输入表名")
            return

        create_sql = f"CREATE TABLE {table_name} ("
        fields = []
        for row in range(self.model_addDB.rowCount()):
            field_name = self.model_addDB.item(row, 0).text().strip()
            if not field_name:
                QMessageBox.warning(self, "警告", "字段名不能为空")
                return
            data_type = self.tableAdd.indexWidget(self.model_addDB.index(row, 1)).currentText()
            length = self.model_addDB.item(row, 2).text().strip()
            decimal_point = self.model_addDB.item(row, 3).text().strip()
            is_null = "NULL" if self.model_addDB.item(row, 4).checkState() == QtGui.Qt.Checked else "NOT NULL"
            is_primary_key = "PRIMARY KEY" if self.model_addDB.item(row, 5).checkState() == QtGui.Qt.Checked else ""
            comment = self.model_addDB.item(row, 6).text().strip()

            field_sql = f"{field_name} {data_type}"
            if data_type in ["TEXT", "REAL"]:
                if length:
                    field_sql += f"({length})"
                if data_type == "REAL" and decimal_point:
                    field_sql += f"({length}, {decimal_point})"
            field_sql += f" {is_null} {is_primary_key} {comment}"
            fields.append(field_sql)

        create_sql += ", ".join(fields)
        create_sql += ");"

        print(create_sql)
        # 在这里执行 SQL 语句
        self.execute_sql(create_sql)

        # 取消按钮

    def cancelWindow(self):
        self.close()